﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Diagnostics;

using SubSonic.Query;
using SubSonic.Repository;

using Northwind.Data;

namespace SubsonicSqlserverDemo
{
    static class ProductsTester
    {
        // *************************************************************** //
        #region [ helper methods ]
        private static void PrintProduct(Product prod)
        {
            Console.WriteLine("id<{0}>-s<{1}>-c<{2}>-\"{3}\"",
                prod.ProductID.ToString(),
                prod.SupplierID.ToString(),
                prod.CategoryID.ToString(),
                prod.ProductName);
        }

        private static void PrintProducts(IEnumerable<Product> products)
        {
            Console.WriteLine("###### TOTAL NUMBER = [{0}] ######", products.Count().ToString());
            int index = 0;
            foreach (Product prod in products)
            {
                ++index;
                Console.Write("[{0}]: ", index.ToString());
                PrintProduct(prod);
            }
        }

        private static Product[] CreateProducts(int count)
        {
            Product[] products = new Product[count];
            for (int index = 0; index < products.Length; ++index)
            {
                products[index] = new Product
                {
                    ProductName = string.Format("cheka-test-{0}", index.ToString()),
                    Discontinued = (index % 2 == 0),
                    QuantityPerUnit = string.Format("test-quantity-{0}", index.ToString())
                };
            }
            return products;
        }
        #endregion

        // *************************************************************** //
        #region [ test methods ]

        private static void TestSelectSingleById()
        {
            Product prod = Product.SingleOrDefault(x => x.ProductID == 4);
            PrintProduct(prod);
        }

        private static void TestSelectNonExist()
        {
            Product prod = Product.SingleOrDefault(x => x.ProductID == -1);
            Debug.Assert(prod == null);
            Console.WriteLine("return null for non-exist items");
        }

        private static void TestSelectMany()
        {
            IEnumerable<Product> products = Product.Find(x => x.CategoryID == 2);
            PrintProducts(products);

            Console.WriteLine("******** further narrow the range ********");
            products = from prod in products
                       where prod.SupplierID == 2
                       select prod;
            PrintProducts(products);
        }

        private static void TestScalar()
        {
            Console.WriteLine("Totally has {0} products", Product.All().Count().ToString());
            Console.WriteLine("Max Id = {0}", Product.All().Max(x => x.ProductID));
        }

        private static void TestInsert()
        {
            Product newProd = new Product
            {
                ProductName = "cheka test",
                Discontinued = true
            };

            var query = Product.All();
            Console.WriteLine("Before inserting, total = {0}, product id = {1}",
                query.Count().ToString(), newProd.ProductID.ToString());

            newProd.Save();
            Console.WriteLine("After inserting, total = {0}, product id = {1}",
                query.Count().ToString(), newProd.ProductID.ToString());
        }

        private static void TestUpdate()
        {
            int prodId = 78;
            Product oriProd = Product.SingleOrDefault(x => x.ProductID == prodId);

            Console.WriteLine("before updating, ");
            PrintProduct(oriProd);

            oriProd.CategoryID = 1;
            oriProd.SupplierID = 2;
            oriProd.QuantityPerUnit = "test";

            oriProd.Update();

            // ---------------------- reselect
            Product updatedProd = Product.SingleOrDefault(x => x.ProductID == prodId);
            Console.WriteLine("original == upated product? {0}", (updatedProd == oriProd).ToString());

            Debug.Assert(updatedProd.CategoryID == oriProd.CategoryID);
            Debug.Assert(updatedProd.SupplierID == oriProd.SupplierID);
            Debug.Assert(updatedProd.QuantityPerUnit == oriProd.QuantityPerUnit);

            Console.WriteLine("after updating, ");
            PrintProduct(updatedProd);
        }

        private static void TestDelete()
        {
            var query = Product.All();
            int oriCount = query.Count();

            Expression<Func<Product, bool>> predicate = x => x.ProductName.Contains("cheka");

            Product.Delete(predicate);

            // ---------------------- check after deleting
            int newCount = query.Count();
            Debug.Assert(newCount == (oriCount - 1));

            var nonExisted = Product.Find(predicate);
            Debug.Assert(0 == nonExisted.Count());

            Console.WriteLine("successfully deleted");
        }

        private static void TestComprehensive()
        {
            // ================================================ insert new
            Product prod = new Product
            {
                ProductName = "cheka test",
                Discontinued = false,
                QuantityPerUnit = "test"
            };
            Debug.Assert(prod.IsNew());

            prod.Add();
            Debug.Assert(!prod.IsNew());
            Console.WriteLine("######### product[{0}] added", prod.ProductID.ToString());

            // ================================================ update
            // !!!!!!!!!!!!!! otherwise when property is modified, it won't set "dirty" flags
            // !!!!!!!!!!!!!! set "IsLoaded" flag to be true, then make this record looking 
            // !!!!!!!!!!!!!! liked loaded from database, though just added into database
            prod.SetIsLoaded(true);
            Debug.Assert(!prod.IsDirty());

            prod.Discontinued = false;// still the orignal value
            Debug.Assert(!prod.IsDirty());

            prod.Discontinued = true;
            prod.QuantityPerUnit = "test-update";

            Debug.Assert(prod.IsDirty());
            var dirtyColumns = prod.GetDirtyColumns();
            foreach (SubSonic.Schema.IColumn col in dirtyColumns)
            {
                Console.WriteLine("COL[{0}] is dirty", col.Name);
            }

            prod.Update();
            Console.WriteLine("######### product[{0}] updated", prod.ProductID.ToString());
            Debug.Assert(!prod.IsDirty());

            // ================================================ delete
            prod.Delete();

            Product nonExisted = Product.SingleOrDefault(x => x.ProductID == prod.ProductID);
            Debug.Assert(nonExisted == null);
            Console.WriteLine("######### product[{0}] deleted", prod.ProductID.ToString());
        }

        private static void TestReference()
        {
            // --------------- use constructor to load from database
            Product prod = Product.SingleOrDefault(x => x.ProductID == 1);

            Category category = prod.Categories.Single();
            Debug.Assert(category.CategoryID == prod.CategoryID);

            Supplier supplier = prod.Suppliers.Single();
            Debug.Assert(supplier.SupplierID == prod.SupplierID);

            Console.WriteLine("reference checking successful");
        }

        /// <summary>
        /// failed experiment on using batch to insert multiple records
        /// </summary>
        private static void TestNonBatchMultiInsert()
        {
            // --------------------- create product
            Product[] products = CreateProducts(5);

            // --------------------- batch insert
            var query = Product.All();
            Console.WriteLine("--- before total rows = {0}", query.Count().ToString());

            /*
             * !!!!!!!!!! it shows that the repository instance returned back
             * !!!!!!!!!! by GetRepo doesn't support batch insert
             * !!!!!!!!!! below codes, either using "prod.Add" or using "repository.Add" both succeeds
             * !!!!!!!!!! so shows that the repository returned back only supports single insert
            IRepository<Product> repository = Product.GetRepo();
            repository.Add(products);
             * **/
            IRepository<Product> repository = Product.GetRepo();
            foreach (Product prod in products)
            {
                // prod.Add();
                repository.Add(prod);
            }

            Console.WriteLine("+++ after inserting {0} rows, now total rows = {1}",
                products.Length.ToString(), query.Count().ToString());
        }

        private static void TestBatchInsert()
        {
            Product[] products = CreateProducts(6);

            // -------------------------------- prepare batch
            NorthwindDB db = new NorthwindDB();
            BatchQuery batchquery = new BatchQuery(db.Provider, db.QueryProvider);

            /*
            var inserts = from prod in products
                          select db.Insert.Into<Product>(x => x.ProductName, x => x.Discontinued).Values(prod.ProductName, prod.Discontinued);
             * */

            var inserts = from prod in products
                          select db.Insert.Into<Product>("ProductName", "Discontinued").Values(prod.ProductName, prod.Discontinued);

            /*
            var inserts = from prod in products
                          select db.Insert.Into<Product>("Products.ProductName", "Products.Discontinued").Values(prod.ProductName, prod.Discontinued);
             * */

            /*
            foreach (Insert insert in inserts)
                insert.Execute();

            Console.WriteLine("DONE !!");
            return;
             * */
            foreach (Insert insert in inserts)
                batchquery.Queue(insert);

            // -------------------------------- batch insert
            var selectAll = Product.All();
            Console.WriteLine("--- before total rows = {0}", selectAll.Count().ToString());

            // db.ExecuteBatch();
            batchquery.Execute();

            Console.WriteLine("+++ after inserting {0} rows, now total rows = {1}",
                products.Length.ToString(), selectAll.Count().ToString());
        }

        // ===================================== //
        #region " failed codes to be posted on stackoverflow "

        private static void SucceedByMultiExecuteInsert()
        {
            Product[] products = CreateProducts(2);

            // -------------------------------- prepare batch
            NorthwindDB db = new NorthwindDB();

            var inserts = from prod in products
                          select db.Insert.Into<Product>(x => x.ProductName, x => x.Discontinued).Values(prod.ProductName, prod.Discontinued);

            // -------------------------------- batch insert
            var selectAll = Product.All();
            Console.WriteLine("--- before total rows = {0}", selectAll.Count().ToString());

            foreach (Insert insert in inserts)
                insert.Execute();

            Console.WriteLine("+++ after inserting {0} rows, now total rows = {1}",
                products.Length.ToString(), selectAll.Count().ToString());
        }

        private static void FailByBatchInsert()
        {
            Product[] products = CreateProducts(2);

            // -------------------------------- prepare batch
            NorthwindDB db = new NorthwindDB();
            BatchQuery batchquery = new BatchQuery(db.Provider, db.QueryProvider);

            var inserts = from prod in products
                          select db.Insert.Into<Product>(x => x.ProductName, x => x.Discontinued).Values(prod.ProductName, prod.Discontinued);

            foreach (Insert insert in inserts)
                batchquery.Queue(insert);

            // -------------------------------- batch insert
            var selectAll = Product.All();
            Console.WriteLine("--- before total rows = {0}", selectAll.Count().ToString());

            batchquery.Execute();

            Console.WriteLine("+++ after inserting {0} rows, now total rows = {1}",
                products.Length.ToString(), selectAll.Count().ToString());
        }

        #endregion

        #endregion

        // *************************************************************** //
        #region [ main entry ]
        public static void TestMain()
        {
            // TestSelectSingleById();
            // TestSelectNonExist();
            // TestSelectMany();
            // TestScalar();
            // TestInsert();
            // TestUpdate();
            // TestDelete();
            // TestComprehensive();
            // TestReference();
            // TestNonBatchMultiInsert();
            // TestBatchInsert();

            // SucceedByMultiExecuteInsert();
            FailByBatchInsert();
        }
        #endregion
    }
}